# coding=utf-8
import pandas as pd
import csv
from model.config import conn

# 定义文件路径和城市名称
files = ['./data/潼南.csv', './data/合川.csv', './data/渝北.csv', './data/巴南.csv']
cities = ['潼南', '合川', '渝北', '巴南']

# 创建一个空的 DataFrame 用于存储合并后的数据
combined_df = pd.DataFrame()

# 循环读取文件并进行处理
for file, city in zip(files, cities):
    # 读取 CSV 文件
    df = pd.read_csv(file, encoding='gbk')

    # 数据变换
    df['日期'] = df['日期'].str.split(' ').str[0]
    df['最高温'] = df['最高温'].str.replace('°', '')
    df['最低温'] = df['最低温'].str.replace('°', '')
    # 添加城市列
    df['城市'] = city

    # 将当前 DataFrame 追加到合并的 DataFrame 中
    combined_df = pd.concat([combined_df, df], ignore_index=True)

# 重新排列列的顺序，将城市列放在最前面
combined_df = combined_df[['城市'] + [col for col in combined_df.columns if col != '城市']]

# 保存处理后的数据
with open('data/data.csv', 'w', encoding='gbk') as f:
    combined_df.to_csv(f, index=False)


cursor = conn.cursor()

# 打开CSV文件
with open('./data/data.csv', newline='', encoding='gbk') as csvfile:
    csv_reader = csv.reader(csvfile)
    next(csv_reader)  # 跳过表头行

    # 读取CSV中的每一行并插入到数据库中
    for row in csv_reader:
        city = row[0]
        date = row[1]
        max_temp = int(row[2])
        min_temp = int(row[3])
        weather = row[4]
        wind_direction_power = row[5]

        # 处理空气质量指数列，考虑空值情况
        air_quality = row[6].strip() if row[6] not in ('', '-') else None

        # 插入数据到数据库
        sql = '''
        INSERT INTO data (city, date, max_temp, min_temp, weather, wind_direction_power, air_quality)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        '''
        cursor.execute(sql, (city, date, max_temp, min_temp, weather, wind_direction_power, air_quality))

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()